from sqlalchemy import *
from sqlalchemy.exc import SAWarning
import re

'''
SQL ALCHEMY BASIC MODEL GENERATOR V0.3 BETA
@author: Marcin Kuzminski
'''


class Params(object):
    ''' all settings class '''

    #DB CONNECTION
    #dialect to specify. should work for postgres and mysql now
    dialect = ''

    #db user name to connect
    user = ''

    #password to connect to database
    password = ''

    #host to connect 
    host = ''

    #database name to connect
    db_name = ''

    #schema to connecto to if this
    schema = None

    # list of tables to autofetch the information for
    table_list = []

    #Specify table class name if  this is None it uses CameCase notation (eg for user_password it Creates UserPassword)
    class_name = None

    #set the metadata associated with this colmn set
    metadata_name = 'Not defined metadata'

    #specify the backref names if None it will uses generated #TODO:fix generation of backrefs
    fk_backref_name = None

    #generate the imports at beginning generate string with all needed imports in order to work with this model
    #generally you should leave this to True if you generating model only set it to false.
    with_imports = True

class BaseModelGenerator(object):

    def __init__(self, params):

        con_str = '%s://%s:%s@%s/%s' \
        % (params.dialect, params.user, params.password, params.host, params.db_name)
        self.temp_meta = MetaData(bind = create_engine(con_str))
        self.params = params
        self.imports = """%s\n%s\n%s\n%s\n%s\n%s\n%s\n\n%s\n """\
        % ('from sqlalchemy.ext.declarative import declarative_base',
            'from sqlalchemy.orm import relation, backref',
            'from sqlalchemy import ForeignKey, Column, Table, Sequence',
            'from sqlalchemy.types import *',
            'from sqlalchemy.databases.mysql import *',
            'from sqlalchemy.databases.postgres import *',
            'from xxxxx import %s' % params.metadata_name,
            'Base = declarative_base(metadata = %s)' % params.metadata_name)


        self.class_name_def = """class %s(Base): """
        self.def_string = '    %(name)s = Column("%(c_name)s", %(type)s, nullable=%(nullable)s,unique=%(uniq)s,default=%(default)s)'
        self.def_string_pk = '    %(name)s = Column("%(c_name)s", %(type)s, nullable=%(nullable)s,unique=%(uniq)s,default=%(default)s, primary_key = %(primary_key)s)'
        self.def_string_fk = '    %(name)s = Column("%(c_name)s", %(type)s,%(fk_col)s,nullable=%(nullable)s,unique=%(uniq)s,default=%(default)s)'
        self.def_string_pk_fk = '    %(name)s = Column("%(c_name)s", %(type)s,%(fk_col)s,nullable=%(nullable)s,unique=%(uniq)s,default=%(default)s,primary_key = %(primary_key)s)'
        self.fk_set = []

    def get_class_name(self, table_name):
        if self.params.class_name is None:
            #pattern to replace _ and - to ' ' in order to make CamelCase
            tmp_class = table_name.replace("_", ' ').replace("-", ' ')
            return self.camelcase(tmp_class)
        else:
            return table_name

    def my_capitalize(self, value):
        ''' capitalize string '''
        return str(value[0].upper()) + "".join([w for w in str(value)[1:]])

    def camelcase(self, value):
        ''' Make camelcase out of table_names '''
        return "".join([self.my_capitalize(w) for w in re.split(re.compile("[\W_]*"), value)])

    def fk_insert(self, fk_orderedSet):
        return ','.join([str(x) for x in fk_orderedSet])

    def auto_fetch_table(self, table_name):
        try:
            tbl = Table(table_name, self.temp_meta, autoload = True, schema = self.params.schema)
        except (SAWarning, Warning) as warn:
            pass
        return tbl

    def check_for_pk(self, table_obj):
        pk_exist = False

        for table in table_obj.columns:
            pk_exist = table.primary_key != False
            if pk_exist:
                break

        if pk_exist is False:
            str = '[%s] table is missing a PRIMARY KEY fix that error your model will not work with table without PK' % table_obj.name
            raise Exception(str)

    def fk_defines(self, column, table_obj):


        #TODO: ADD FUNC for more than one FK
        fk = column.foreign_keys[0]._colspec.split('.')

        if self.params.schema == None or self.params.schema == '':
            col_desc_proper = fk[0]
            fk_backref_name = "%s_on_%s" % (table_obj.name , col_desc_proper)
        else:
            col_desc_proper = fk[1]
            fk_backref_name = "%s_on_%s" % (table_obj.name , col_desc_proper)

        pj = 'primaryjoin = "%s.%s==%s.%s"' % (self.camelcase(table_obj.name), column.name, self.camelcase(fk[0]) , fk[1])

        rel = """    %s = relation('%s', backref='%s',%s)""" % \
        (table_obj.name + '_' + col_desc_proper + '_rel', self.camelcase(col_desc_proper), fk_backref_name, pj)

        self.fk_set.append(rel)

    def generate_table_string(self, table_name):

        print self.class_name_def % self.get_class_name(table_name)

        print "    __tablename__ = '%s'" % (table_name)

        if self.params.schema:
            print "    __table_args__ = {'schema':'%s','useexisting':True}" % (self.params.schema)
        else:
            print "    __table_args__ = {'useexisting':True}"

        auto_table = self.auto_fetch_table(table_name)

        #check if table has a PK if not this function will raise an exception
        self.check_for_pk(auto_table)

        for i in auto_table.columns:

            if i.primary_key and not i.foreign_keys:
                print self.def_string_pk % {'name':i.name,
                                       'c_name':i.name,
                                       'type':i.type,
                                       'nullable':i.nullable,
                                       'uniq':i.unique,
                                       'default':i.default,
                                       'primary_key':i.primary_key}

            elif i.foreign_keys and not i.primary_key:
                print self.def_string_fk % {'name':i.name,
                                    'c_name':i.name,
                                    'type':i.type,
                                    'nullable':i.nullable,
                                    'uniq':i.unique,
                                    'default':i.default,
                                    'fk_col':self.fk_insert(i.foreign_keys)}
                self.fk_defines(i, auto_table)

            elif i.foreign_keys and i.primary_key:
                print self.def_string_pk_fk % {'name':i.name,
                                    'c_name':i.name,
                                    'type':i.type,
                                    'nullable':i.nullable,
                                    'uniq':i.unique,
                                    'default':i.default,
                                    'primary_key':i.primary_key,
                                    'fk_col':self.fk_insert(i.foreign_keys)}
                self.fk_defines(i, auto_table)

            else:
                print self.def_string % {'name':i.name,
                                    'c_name':i.name,
                                    'type':i.type,
                                    'nullable':i.nullable,
                                    'uniq':i.unique,
                                    'default':i.default}

        if auto_table._foreign_keys:
            print '\n    #definition of foreignkeys backrefs'
            for f in self.fk_set:
                print f

    def generate_model(self):
        if self.params.with_imports:
            print self.imports

        for table_name in self.params.table_list:
            self.fk_set = []
            self.generate_table_string(table_name)
    
    def get_sql_create(self):
        """
        Temporary function.
        """
        from cStringIO import StringIO
        import sys
        tmp = sys.stdout
        capture = StringIO()
        sys.stdout = capture
        self.generate_model()
        sys.stdout = tmp
        capture.seek(0)
        output = capture.read()
        return output

if __name__ == "__main__":
    params = Params()
    params.dialect = 'mysql'
    params.user = 'root'
    params.password = ''
    params.host = '127.0.0.1'
    params.db_name = 'motoshow'
    params.schema = None
    params.table_list = ['cars_testedcar',]
    params.metadata_name = 'ev5_metadata'

    bmg = BaseModelGenerator(params)

    bmg.generate_model()






